﻿'Copyright (C) 2008 Compagnie des Transport Strasbourgeois (CTS)

'This program is free software; you can redistribute it and/or modify
'it under the terms of the GNU General Public License as published by
'the Free Software Foundation; either version 3 of the License, or
'(at your option) any later version.

'This program is distributed in the hope that it will be useful,
'but WITHOUT ANY WARRANTY; without even the implied warranty of
'MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
'GNU General Public License for more details.

'You should have received a copy of the GNU General Public License along
'with this program; if not, write to the Free Software Foundation, Inc.,
'51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
Imports DotNetNuke
Imports System.Web.UI
Imports System.Collections.Generic
Imports System.Reflection
Imports DotNetNuke.Security.PortalSecurity
Imports System.IO

Partial Class DesktopModules_trombinoscope_administration
    Inherits Entities.Modules.PortalModuleBase
    Implements Entities.Modules.IActionable

    Protected Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load, Me.Load
        'On récupère le chemin des photos
        chemin_photos_relatif = cheminPhotos()
        'On génère une image pour l'affichage des miniatures
        Dim image As ImageField
        image = GridView_listeSalaries.Columns(6)
        image.DataImageUrlFormatString = chemin_photos_relatif & "{0}.jpg"

        'En cas de PostBack (clic sur un bouton), on replace la vue au même endroit (pour éviter de devoir scroller pour revenir à la même position)
        Page.MaintainScrollPositionOnPostBack = True

        'On rend invisible les labels de confirmation
        label_ajoutSalarieReussi.Visible = False
        label_ajoutPhotoExiste.Visible = False
        label_ajoutPhotoReussi.Visible = False
        label_matriculeExiste.Visible = False
        label_ajoutServiceErreur.Visible = False
        label_ajoutServiceReussi.Visible = False
        label_ajoutDirectionTaille.Visible = False
        label_ajoutDirectionReussi.Visible = False

        If Not Page.IsPostBack Then
            Label_rechercheNom.Text = "%"
            Label_recherchePrenom.Text = "%"
            Label_rechercheMatricule.Text = "%"
            Label_rechercheService.Text = "%"
        End If
    End Sub

#Region "Optional Interfaces"

    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' Registers the module actions required for interfacing with the portal framework
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    ''' <history>
    ''' </history>
    ''' -----------------------------------------------------------------------------
    Public ReadOnly Property ModuleActions() As Entities.Modules.Actions.ModuleActionCollection Implements Entities.Modules.IActionable.ModuleActions
        Get
            Dim Actions As New Entities.Modules.Actions.ModuleActionCollection
            Actions.Add(GetNextActionID, "Organigramme", "", "", "icon_users_16px.gif", DotNetNuke.Common.Globals.NavigateURL(PortalSettings.ActiveTab.TabID, ""), False, Security.SecurityAccessLevel.View, True, False)
            Actions.Add(GetNextActionID, "Rechercher", "", "", "icon_search_16px.gif", EditUrl("rechercher"), False, Security.SecurityAccessLevel.View, True, False)
            Return Actions
        End Get
    End Property

#End Region
#Region "Constantes"
    Private chemin_photos_relatif As String
    Private requeteRecherche As String
#End Region

    Protected Function cheminPhotos() As String
        Dim chaine_connexion As String
        'Récupération et association de la chaîne de connexion à la bdd
        chaine_connexion = SqlDataSource_salaries.ConnectionString
        'Récupération du chemin des photos
        Dim connection_param As SqlConnection = New SqlConnection(chaine_connexion)
        Dim commande_chemin_photos As SqlCommand = New SqlCommand("SELECT chemin_photos_relatif FROM param_trombinoscope", connection_param)
        connection_param.Open()
        Dim reader_chemin_photos As SqlDataReader = commande_chemin_photos.ExecuteReader()
        Dim chemin_photos As String
        If reader_chemin_photos.HasRows Then
            reader_chemin_photos.Read()
            chemin_photos = reader_chemin_photos.GetString(0)
        End If
        reader_chemin_photos.Close()
        connection_param.Close()
        Return String.Format("{0}", chemin_photos)
    End Function

#Region "Fonctions de Recherche"

    'Requête SQL pour la recherche
    Protected Sub select_recherche()
        Dim requeteService As String
        If Label_rechercheService.Text = "%" Then
            requeteService = "(salaries.service IS NULL OR salaries.service LIKE '%')"
        Else
            requeteService = "salaries.service LIKE '" + Label_rechercheService.Text + "'"
        End If
        Dim requete As String
        requete = "SELECT salaries.id_salaries, salaries.nom, salaries.prenom, salaries.matricule, ISNULL(salaries.service,-1) As service, salaries.fonction, services.nom AS nom_service FROM salaries LEFT OUTER JOIN services ON salaries.service = services.id_services WHERE salaries.nom LIKE '" + Label_rechercheNom.Text + "%' AND salaries.prenom LIKE '" + Label_recherchePrenom.Text + "%' AND " + requeteService + " AND salaries.matricule LIKE '" + Label_rechercheMatricule.Text + "%' ORDER BY salaries.nom"
        requeteRecherche = requete
        SqlDataSource_salaries.SelectCommand = requeteRecherche
    End Sub

    'Lors de la suppression d'un salarié on envoit la requête SQL de SELECT et on supprime la photo associée
    Protected Sub GridView1_RowDeleted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewDeletedEventArgs) Handles GridView_listeSalaries.RowDeleted
        select_recherche()
        File.Delete(MapPath(e.Values(2).ToString))
        miseAJourListesFonctions()
    End Sub

    'Lors de l'édition
    Protected Sub GridView1_RowEditing(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewEditEventArgs) Handles GridView_listeSalaries.RowEditing
        'On refait la requête Select (elle n'est pas conservé au rechargement)
        select_recherche()
    End Sub

    'Lors de la mise à jour
    Protected Sub GridView1_RowUpdated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdatedEventArgs) Handles GridView_listeSalaries.RowUpdated
        Dim chemin As String
        chemin = chemin_photos_relatif & e.NewValues(2).ToString & ".jpg"
        'Si le matricule de la personne change on change le nom de la photo
        If chemin <> e.OldValues(2).ToString Then
            File.Copy(MapPath(e.OldValues(2).ToString), MapPath(chemin))
            File.Delete(MapPath(e.OldValues(2).ToString))
        End If
        'On refait la requête Select (elle n'est pas conservé au rechargement)
        select_recherche()
        'On met à jour le GridView des services
        GridView_services.DataBind()
        'On met à jour la liste des fonctions (dans la partie pour ajouter un salarié) au cas où la personne à modifier la fonction
        insertion_fonction_liste.DataBind()

        miseAJourListesFonctions()
    End Sub

    'Lors de l'annulation de l'édition
    Protected Sub GridView1_RowCancelingEdit(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCancelEditEventArgs) Handles GridView_listeSalaries.RowCancelingEdit
        'On refait la requête Select (elle n'est pas conservé au rechargement)
        select_recherche()
    End Sub

    'Lors d'un tri
    Protected Sub GridView1_Sorted(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView_listeSalaries.Sorted
        'On refait la requête Select (elle n'est pas conservé au rechargement)
        select_recherche()
    End Sub

    'Lors d'un changement d'index
    Protected Sub GridView1_PageIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView_listeSalaries.PageIndexChanged
        'On refait la requête Select (elle n'est pas conservé au rechargement)
        select_recherche()
    End Sub

    'Lors d'un clic sur le bouton rechercher
    Protected Sub bouton_rechercher_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles bouton_rechercher.Click
        Label_rechercheNom.Text = Replace(nom.Text, "'", "''")
        Label_recherchePrenom.Text = Replace(prenom.Text, "'", "''")
        Label_rechercheMatricule.Text = Replace(matricule.Text, "'", "''")
        Label_rechercheService.Text = Replace(Dropdownliste_recherche_serviceListe.SelectedValue, "'", "''")
        nom.Text = ""
        prenom.Text = ""
        matricule.Text = ""
        Dropdownliste_recherche_serviceListe.SelectedIndex = 0
        select_recherche()
    End Sub
#End Region

    'Lors d'un clic sur le bouton edit, on selectionne en même temps la ligne (obligatoire pour récupérer la datakey pour remplir la dropdownlist)
    Protected Sub GridView_services_RowEditing(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewEditEventArgs) Handles GridView_services.RowEditing
        GridView_services.SelectedIndex = e.NewEditIndex.ToString
        'On refait la requête Select (elle n'est pas conservé au rechargement)
        select_service()
    End Sub

    Protected Sub miseAJourListesFonctions()
        insertion_fonction_liste.Items.Clear()
        'On réajoute le choix nouvelle fonction qui a été effacer
        Dim nv_fct As ListItem = New ListItem("Nouvelle fonction", "nv_fct")
        insertion_fonction_liste.Items.Insert(0, nv_fct)
        insertion_fonction_liste.DataBind()
    End Sub

    Protected Sub bouton_insertion_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles bouton_insertion.Click
        'Si un nom et un prenom a été entrer
        'If insertion_nom.Text.Length > 0 And insertion_prenom.Text.Length > 0 Then

        'On regarde si il y a déjà un employé avec ce matricule
        Dim chaine_connexion As String
        chaine_connexion = SqlDataSource_salaries.ConnectionString
        Dim connection As SqlConnection = New SqlConnection(chaine_connexion)
        Dim commande As SqlCommand = New SqlCommand("SELECT COUNT(id_salaries) AS Expr1 FROM salaries WHERE (matricule = '" & Replace(insertion_matricule.Text, "'", "''") & "')", connection)
        connection.Open()
        Dim reader As SqlDataReader = commande.ExecuteReader
        Dim nombre_matricule As Integer
        reader.Read()
        nombre_matricule = reader.GetInt32(0)
        reader.Close()
        connection.Close()

        'Si non on ajout l'employé
        If (nombre_matricule = 0) Then
            Dim requete As String
            Dim param_fonction As String
            'Si la personne n'a pas entré une nouvelle fonction
            If insertion_fonction_liste.SelectedValue <> "nv_fct" Then
                'On prend celle dans la liste déroulante
                param_fonction = insertion_fonction_liste.SelectedValue
            Else
                'Sinon on prend celle entrée par la personne
                param_fonction = Replace(insertion_fonction.Text, "'", "''")
            End If
            requete = "INSERT INTO salaries(nom, prenom, matricule, service, fonction) VALUES (ISNULL('" & Replace(insertion_nom.Text, "'", "''") & "',''),ISNULL('" & Replace(insertion_prenom.Text, "'", "''") & "',''),'" & Replace(insertion_matricule.Text, "'", "''") & "','" & insertion_service.SelectedValue & "','" & param_fonction & "')"
            SqlDataSource_salaries.InsertCommand = requete
            SqlDataSource_salaries.Insert()
            insertion_fonction_liste.Items.Clear()
            insertion_fonction_liste.DataBind()

            miseAJourListesFonctions()

            'Si la personne a envoyé une photo
            If insertion_photo.HasFile Then
                Dim chemin As String = MapPath(cheminPhotos)
                Dim nom_fichier As String = insertion_matricule.Text & ".jpg"
                chemin += nom_fichier
                'On enregistre la photo dans le bon repertoire
                insertion_photo.SaveAs(chemin)
            End If
            label_ajoutSalarieReussi.Text = "Ajout réussi (" & insertion_prenom.Text & " " & insertion_nom.Text & ")"
            label_ajoutSalarieReussi.Visible = True
            insertion_nom.Text = ""
            insertion_prenom.Text = ""
            insertion_matricule.Text = ""
            insertion_fonction.Text = ""
            insertion_fonction_liste.SelectedIndex = 0
            insertion_service.SelectedIndex = -1

        Else
            'Sinon on affiche un message d'erreur
            label_matriculeExiste.Visible = True
        End If
        'End If
    End Sub

    Protected Sub bouton_insertion_photo_seule_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles bouton_insertion_photo_seule.Click
        'Si la personne a entré un matricule et a envoyé un fichier
        If matricule_photo_seule.Text.Length <> 0 And insertion_photo_seule.HasFile Then
            Dim chaine_connexion As String
            chaine_connexion = SqlDataSource_salaries.ConnectionString
            Dim connection As SqlConnection = New SqlConnection(chaine_connexion)
            'On cherche combien de salarié ont ce matricule
            Dim commande As SqlCommand = New SqlCommand("SELECT COUNT(id_salaries) AS Expr1 FROM salaries WHERE (matricule = '" & Replace(matricule_photo_seule.Text, "'", "''") & "')", connection)
            connection.Open()
            Dim reader As SqlDataReader = commande.ExecuteReader
            Dim nombre_matricule As Integer
            reader.Read()
            nombre_matricule = reader.GetInt32(0)
            'Si il y a au moins 1 salarié qui a ce matricule on enregistre la photo
            If (nombre_matricule > 0) Then
                Dim chemin As String = MapPath(cheminPhotos)
                Dim nom_fichier As String = matricule_photo_seule.Text & ".jpg"
                chemin += nom_fichier
                insertion_photo_seule.SaveAs(chemin)
                GridView_listeSalaries.DataBind()
                label_ajoutPhotoReussi.Visible = True
            Else
                'Sinon on affiche un message d'erreur
                label_ajoutPhotoExiste.Text = "Il n'y a aucun salarié ayant comme matricule " & matricule_photo_seule.Text
                label_ajoutPhotoExiste.Visible = True
            End If
            reader.Close()
            connection.Close()
            matricule_photo_seule.Text = ""
        End If
    End Sub

    Protected Sub miseAJourListesServices()
        parent_ajout_service.Items.Clear()
        'On réajoute le choix aucun parent qui a été effacer
        Dim aucun_parent As ListItem = New ListItem("Aucun parent", "")
        parent_ajout_service.Items.Insert(0, aucun_parent)
        parent_ajout_service.DataBind()
        insertion_service.Items.Clear()
        insertion_service.DataBind()
        Dropdownliste_recherche_serviceListe.Items.Clear()
        Dim non_precise As ListItem = New ListItem("Non précisé", "%")
        Dropdownliste_recherche_serviceListe.Items.Insert(0, non_precise)
        Dropdownliste_recherche_serviceListe.DataBind()
    End Sub

    Protected Sub bouton_ajout_service_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles bouton_ajout_service.Click
        'Si la personne a entré un ID et un nom de service
        If nom_ajout_service.Text.Length > 0 Then
            Dim requete_ajout_parent As String
            Dim requete_ajout_direction As String

            Dim connexion_existe As SqlConnection = New SqlConnection(SqlDataSource_admin_services.ConnectionString)
            Dim commande_existe As SqlCommand = New SqlCommand("SELECT COUNT(*) AS Expr1 FROM services WHERE (nom = '" & Replace(nom_ajout_service.Text, "'", "''") & "')", connexion_existe)
            connexion_existe.Open()
            Dim reader_existe As SqlDataReader = commande_existe.ExecuteReader
            Dim serviceExistant As Boolean = False
            If reader_existe.HasRows Then
                reader_existe.Read()
                If reader_existe.GetInt32(0) > 0 Then
                    serviceExistant = True
                End If
            End If
            reader_existe.Close()
            connexion_existe.Close()

            'Si le service n'existe pas déjà
            If serviceExistant = False Then
                'On vérifie si la personne a entré un parent et une direction
                'Si non on prépare les variables pour insérer la valeur NULL dans la base
                If parent_ajout_service.Text = "" Then
                    requete_ajout_parent = "Null"
                Else
                    requete_ajout_parent = "'" & parent_ajout_service.SelectedValue & "'"
                End If
                If DropDownList_ajout_service_direction.SelectedValue = "" Then
                    requete_ajout_direction = "Null"
                Else
                    requete_ajout_direction = "'" & DropDownList_ajout_service_direction.SelectedValue & "'"
                End If
                Dim requete As String
                requete = "INSERT INTO services(nom, parent, direction) VALUES ('" & Replace(nom_ajout_service.Text, "'", "''") & "'," & requete_ajout_parent & "," & requete_ajout_direction & ")"
                SqlDataSource_admin_services.InsertCommand = requete
                'On ajoute le service
                SqlDataSource_admin_services.Insert()
                'On met à jour les listes déroulantes
                miseAJourListesServices()
                'On affiche un message indiquant la réussite
                label_ajoutServiceReussi.Text = "Ajout réussi (" & nom_ajout_service.Text & ")"
                label_ajoutServiceReussi.Visible = True
                'On remet les champs à zéro
                nom_ajout_service.Text = ""
                parent_ajout_service.SelectedIndex = 0
                DropDownList_ajout_service_direction.SelectedIndex = 0
            Else
                label_ajoutServiceErreur.Text = "Un service de ce nom existe déjà"
                label_ajoutServiceErreur.Visible = True
            End If
        Else
            label_ajoutServiceErreur.Text = "Vous n'avez pas entré de nom pour le service"
            label_ajoutServiceErreur.Visible = True
        End If
    End Sub

    Protected Sub select_service()
        Dim requete As String
        requete = "SELECT services.id_services, services.nom, services_parent.nom AS Parent, services.id_chef_de_service_1, services.id_chef_de_service_2, services.id_secretaire_1, services.id_secretaire_2, salaries_chef_service_1.nom + ' ' + salaries_chef_service_1.prenom AS 'Chef service 1', salaries_chef_service_2.nom + ' ' + salaries_chef_service_2.prenom AS 'Chef service 2', salaries_secretaire_1.nom + ' ' + salaries_secretaire_1.prenom AS 'Secrétaire 1', salaries_secretaire_2.nom + ' ' + salaries_secretaire_2.prenom AS 'Secrétaire 2', services.parent AS services_id_parent, directions.nom AS Direction, directions.id_direction FROM services LEFT OUTER JOIN services AS services_parent ON services.parent = services_parent.id_services LEFT OUTER JOIN salaries AS salaries_secretaire_2 ON services.id_secretaire_2 = salaries_secretaire_2.id_salaries LEFT OUTER JOIN salaries AS salaries_secretaire_1 ON services.id_secretaire_1 = salaries_secretaire_1.id_salaries LEFT OUTER JOIN salaries AS salaries_chef_service_2 ON services.id_chef_de_service_2 = salaries_chef_service_2.id_salaries LEFT OUTER JOIN salaries AS salaries_chef_service_1 ON services.id_chef_de_service_1 = salaries_chef_service_1.id_salaries LEFT OUTER JOIN directions ON services.direction = directions.id_direction WHERE services.nom LIKE '" & Replace(recherche_nom_service.Text, "'", "''") & "%' ORDER BY services.nom"
        SqlDataSource_admin_services.SelectCommand = requete
    End Sub

    Protected Sub bouton_recherche_service_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles bouton_recherche_service.Click
        select_service()
    End Sub

    Protected Sub GridView_services_PageIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView_services.PageIndexChanged
        'On refait la requête Select (elle n'est pas conservé au rechargement)
        select_service()
    End Sub

    Protected Sub GridView_services_RowCancelingEdit(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCancelEditEventArgs) Handles GridView_services.RowCancelingEdit
        'On refait la requête Select (elle n'est pas conservé au rechargement)
        select_service()
    End Sub

    Protected Sub GridView_services_RowUpdated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdatedEventArgs) Handles GridView_services.RowUpdated
        'On refait la requête Select (elle n'est pas conservé au rechargement)
        select_service()
        'On met à jour le GridView
        GridView_listeSalaries.DataBind()
        'On met à jour les listes utilisant les noms de service au cas où la personne à changer le nom
        miseAJourListesServices()
    End Sub

    Protected Sub GridView_services_Sorted(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView_services.Sorted
        'On refait la requête Select (elle n'est pas conservé au rechargement)
        select_service()
    End Sub

    Protected Sub GridView_services_RowDeleted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewDeletedEventArgs) Handles GridView_services.RowDeleted
        'On refait la requête Select (elle n'est pas conservé au rechargement)
        select_service()
        miseAJourListesServices()
    End Sub

    Protected Sub miseAJourListesDirections()
        DropDownList_ajout_service_direction.Items.Clear()
        'On réajoute le choix aucune qui a été effacer
        Dim aucune As ListItem = New ListItem("Aucune", "")
        DropDownList_ajout_service_direction.Items.Insert(0, aucune)
        DropDownList_ajout_service_direction.DataBind()
    End Sub

    Protected Sub bouton_insertion_direction_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles bouton_insertion_direction.Click
        'Si l'utilisateur a entré un nom de direction
        If insertion_direction.Text.Length > 0 Then
            Dim requete As String
            requete = "INSERT INTO directions(nom) VALUES ('" & Replace(insertion_direction.Text, "'", "''") & "')"
            SqlDataSource_liste_directions.InsertCommand = requete
            'On ajoute la direction
            SqlDataSource_liste_directions.Insert()

            'On met à jour la liste de choix de la direction pour l'ajout d'un service
            miseAJourListesDirections()

            insertion_direction.Text = ""
            label_ajoutDirectionReussi.Visible = True
        Else
            label_ajoutDirectionTaille.Visible = True
        End If
    End Sub

    Protected Sub GridView_directions_RowUpdated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdatedEventArgs) Handles GridView_directions.RowUpdated
        'On met à jour le GridView des services
        GridView_services.DataBind()
        miseAJourListesDirections()
    End Sub

    Protected Sub Button_administration_service_direction_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button_administration_service_direction.Click
        'Selon quelle partie était affiché on affiche l'autre et on change le texte du bouton
        If Panel1_administration_direction_service.Visible = 0 Then
            Panel1_administration_direction_service.Visible = 1
            Panel1_administration_salaries.Visible = 0
            Button_administration_service_direction.Text = "Administration des salariés"
        Else
            Panel1_administration_direction_service.Visible = 0
            Panel1_administration_salaries.Visible = 1
            Button_administration_service_direction.Text = "Administration des services et des directions"
        End If
    End Sub

    Protected Sub GridView_services_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView_services.DataBound
        If GridView_services.Rows.Count <= 0 Then
            label_serviceExiste.Visible = True
        Else
            label_serviceExiste.Visible = False
        End If
    End Sub

    Protected Sub GridView1_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView_listeSalaries.DataBound
        If GridView_listeSalaries.Rows.Count <= 0 Then
            label_salarieExiste.Visible = True
        Else
            label_salarieExiste.Visible = False
        End If
    End Sub

    Protected Sub GridView_directions_RowDeleted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewDeletedEventArgs) Handles GridView_directions.RowDeleted
        miseAJourListesDirections()
    End Sub

End Class